/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package callProcs;

import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.DriverManager;
import java.sql.NClob;
import java.sql.Ref;
import java.sql.ResultSetMetaData;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Date;
import java.util.Map;

/**
 *
 * @author Javier
 */
public class ProcsAlumno 
{
    
    public ProcsAlumno() //throws SQLException
    {
//        con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","1234");
    }
            
     public void insertaralumno(String carne, String nombre, String apellido,String FechaNac,boolean estadoCivil,String NoRegistro,int NoOrden,BigInteger dpi,  int direccion) throws SQLException{
            java.sql.Connection con = null;
            con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");

            String banderaEC=null;
            String fechaNac = null;
            if (estadoCivil) banderaEC = "true";
            if (!estadoCivil) banderaEC = "false";
            fechaNac = String.valueOf(FechaNac);
//            NoCarne int, NoBufete int, NomEstudiante varchar(45), ApeEstudiante varchar(45),
//            FechaNacimiento date, EstadoCivil bit, NoRegistro char(4), NoOrden int, dpi bigint,
//            idDireccion int

            java.sql.CallableStatement cs = con.prepareCall("{call InsertarEstudiante(" + carne + ", '" + nombre + "', '" + apellido + "', '" +
                                                            FechaNac + "', " + banderaEC + ", '" + NoRegistro + "', " + NoOrden + ", " + dpi 
                                                            + ", " + String.valueOf(direccion) + ")}");
            java.sql.Statement stmt  = con.createStatement();
            java.sql.ResultSet rs = cs.executeQuery();
    }
     
     public void InsertaDirecAlumno(String direccion, int zona, int IdMuni) throws SQLException
     {
         java.sql.Connection con = null;     
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
              java.sql.CallableStatement cs = con.prepareCall("{call InsertarDireccionEstudiante('" + direccion + "', " + String.valueOf(zona) + ", " + String.valueOf(IdMuni) + ")}");
              java.sql.Statement stm = con.createStatement();
              java.sql.ResultSet rs = cs.executeQuery();
     }
     
     public void InsertaTelAlumno(String tel, int carne, int tipo) throws SQLException
     {
         java.sql.Connection con = null;
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.CallableStatement cs = con.prepareCall("{call InsertarTelefonoEstudiante('" + tel + "', " + String.valueOf(carne) + "," + String.valueOf(tipo) + ")}");
         java.sql.Statement stm = con.createStatement();
         java.sql.ResultSet rs = cs.executeQuery();
     }
     
     public java.sql.ResultSet ObtenerMuni(int idDep, String nombre) throws SQLException
     {
         java.sql.Connection con = null;
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.ResultSet rs = null;
         Statement cs = con.createStatement();
         //rs = cs.executeQuery("SELECT idMunicipio FROM municipio where Departamento_idDepartamento = 1 and Nombre_municipio like Cob%") ;
         rs = cs.executeQuery("select idMunicipio from municipio where Departamento_idDepartamento = " + String.valueOf(idDep) + " and Nombre_municipio =" + "'"+nombre+"'");
         return rs;
     }
     
     public java.sql.ResultSet MaximaDireccion() throws SQLException
     {
         java.sql.Connection con = null;
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.ResultSet rs = null;
         Statement cs = con.createStatement();
         rs = cs.executeQuery("select max(idDireccion_estudiante) from direccion_estudiante");
         return rs;
     }
      
//     public java.sql.ResultSet MaxCarne() throws SQLException
//     {
//         java.sql.Connection con = null;
//         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","1234");
//         java.sql.ResultSet rs = null;
//         Statement cs = con.createStatement();
//         rs = cs.executeQuery("select max(NoCarne) from estudiante");
//         return rs;
//     }
     
     //-----------------------------------------inicio procedimientos actualizar alumno-----------------------------------------------------------
     public java.sql.ResultSet ObtieneCarne() throws SQLException
     {
         java.sql.Connection con = null;
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.ResultSet rs = null;
         Statement cs = con.createStatement();
         rs = cs.executeQuery("select NoCarne from estudiante");
         return rs;
     }
     
     public java.sql.ResultSet VerDatos(String carne) throws SQLException
     {
         java.sql.Connection con = null;
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.ResultSet rs = null;
         Statement cs = con.createStatement();
         rs = cs.executeQuery("select NoBufete, Nombre_estudiante, Apellido_estudiante, Estado_civil_estudiante, NoReg_estudiante, NoOrden_estudiante, Dpi_estudiante, Direccion_estudiante_idDireccion_estudiante, Dpi_estudiante from estudiante where NoCarne =" + carne);
         return rs;
     }
     
     public java.sql.ResultSet ObtieneDir(String carne) throws SQLException
     {
         java.sql.Connection con = null;
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.ResultSet rs = null;
         Statement cs = con.createStatement();
         rs = cs.executeQuery("select dir.idDireccion_estudiante, dir.Direccion_estudiante, dir.Zona, muni.Nombre_municipio, dep.Nombre_departamento from direccion_estudiante as dir inner join estudiante as es on es.Direccion_estudiante_idDireccion_estudiante = dir.idDireccion_estudiante "
                 + "inner join municipio as muni on muni.idMunicipio = dir.Municipio_idMunicipio inner join departamento as dep on dep.idDepartamento = muni.Departamento_idDepartamento where es.NoCarne = " + carne );
         return rs;
     }
     
     public java.sql.ResultSet ObtenerTel(String carne) throws SQLException
     {
         java.sql.Connection con = null;
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.ResultSet rs = null;
         Statement cs = con.createStatement();
         rs = cs.executeQuery("select tel.telefono, tipo.NombreTipo, tel.idTelefono from telefonoestudiante as tel inner join tipotelefono as tipo on tipo.idTipoTel = tel.TipoTelefono_idTipoTel where Estudiante_NoCarne = " + carne);
         return rs;
     }
     
     public void UpdtDir(String idDir, String dir, String zone, String idMuni) throws SQLException
     {
         java.sql.Connection con = null;     
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.CallableStatement cs = con.prepareCall("{call Actualiza_direccion_estudiante(" + idDir + ",' " + dir + "', " + zone +","+ idMuni +")}");
         java.sql.Statement stm = con.createStatement();
         java.sql.ResultSet rs = cs.executeQuery();
     }
     
//(carne int, bufete int, nombre varchar(45), apellido varchar(45), FechaNac date, estado bit, registro char(4), orden int, dpi bigint, idasesor int, iddireccion int)
     public void UpdtEst(String carne, String nombre, String apellido, boolean estado, String reg, int orden, String dpi, String iddir) throws SQLException
     {
         String ec = "";
         java.sql.Connection con = null;
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         String banderaEC=null;
         String fechaNac = null;
         if (estado) ec = "true";
         if (!estado) ec = "false";
         java.sql.CallableStatement cs = con.prepareCall("{call Actualiza_estudiante(" + carne + ", '" + nombre + "','" + apellido + "'," + 
                 ec +",'" + reg + "'," + String.valueOf(orden)+"," + dpi + "," + iddir + ")}" );
         java.sql.Statement stmt  = con.createStatement();
         java.sql.ResultSet rs = cs.executeQuery();

     }
     
     public void UpdTel(String id, String tel, String carne, String tipo) throws SQLException
     {
         java.sql.Connection con = null;     
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.CallableStatement cs = con.prepareCall("{call Actualiza_tel_estudiante("+ id + ",'" + tel + "'," + carne + "," + tipo +")}" );
         java.sql.Statement stmt  = con.createStatement();
         java.sql.ResultSet rs = cs.executeQuery();
     }
     
 //--------------------------------------------------------procedimientos Usuarios----------------------------------------------------------------
     
     public void IngresaUser(String user, String pass, String name, String ape) throws SQLException
     {
         java.sql.Connection con = null;     
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.CallableStatement cs = con.prepareCall("{call InsertarUsuario('" + user + "','" + pass + "','" + name + "','" +ape + "')}");
         java.sql.Statement stmt = con.createStatement();
         java.sql.ResultSet rs = cs.executeQuery();
     }
     
     public java.sql.ResultSet VerificaUser(String usuario, String pass) throws SQLException
     {
         java.sql.Connection con = null;
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.ResultSet rs = null;
         Statement cs = con.createStatement();
         rs = cs.executeQuery("select count(nombreUsuario) as cont, nombreUsuario, apellidoUsuario from usuario where idUsuario = '" + usuario + "' and contrasena = '" + pass + "'");
         return rs;
     }
     
//-------------------------------------------------------proc para el caso----------------------------------------------------------------------
     
     public void Caso_est(String carne, String folio) throws SQLException
     {
          java.sql.Connection con = null;     
          con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
          java.sql.CallableStatement cs = con.prepareCall("{call asigna_casoest(" + carne +","+ folio + ")}");
          java.sql.Statement stm = con.createStatement();
          java.sql.ResultSet rs = cs.executeQuery();
     }
     
     public java.sql.ResultSet VerDataCaso(String folio) throws SQLException
     {
         java.sql.Connection con = null;
         con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","123");
         java.sql.ResultSet rs = null;
         Statement cs = con.createStatement();
         rs = cs.executeQuery("select Descripcion_caso, Fecha_inicio from caso where No_folio = " + folio );
         return rs;
     }
}
